/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.ban.ycl.st.load.util;

import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import javax.sql.rowset.serial.SerialClob;
import org.apache.log4j.Logger;

/**
 *
 * @author Administrator
 */
public class StHelper {

    private static Connection conn = null;
    private static final Logger logger = Logger.getLogger(StHelper.class.getName());

//	private static GlobalConfig gConfig;
//	
//	public STDbUtilConnection(GlobalConfig gc) {
//		logger = LoggerFactory.getLogger(STDbUtilConnection.class);
//		gConfig = gc;
//	}
    private static void init() {
        try {
            Class.forName("com.oscar.Driver");
//            conn = DriverManager.getConnection("jdbc:oscar://10.136.128.42:2011/BUZ_DB", "TMP_USR", "TMP_USR"); //IP，用户名，密码需要更换为对应库的IP。
            conn = DriverManager.getConnection("jdbc:oscar://10.136.128.42:2011/BUZ_DB", "ZBMS_USR", "ZBMS_USR");

        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
    }

    public static Connection getConnection() {
        try {
            if (conn != null && !conn.isClosed()) {
                return conn;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        init();
        return conn;
    }

    public static void select() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM ZBMS_TX_DAYHOTTOPIC LIMIT 5");
        //如果有数据，rs.next()返回true
        while (rs.next()) {
            logger.info(rs.getString("CREATETIME") + " ：" + rs.getLong("PVIEWS") + " : " + rs.getString("URL"));
        }
    }

    public static void insert(List<HashMap<String, Object>> valueMaplist) throws SQLException {
        if (conn != null && !conn.isClosed()) {
            conn = getConnection();
        }
        PreparedStatement stmt = null;
        try {
            for (int i = 0; i < valueMaplist.size(); i++) {
                String sql = "INSERT INTO  ZBMS_TX_WEEKHOTTOPIC (CREATETIME ,SORTID,TITLE ,CONTENTS,URL,PVIEWS,LIKENUM,HOTVALUE,HOT_SORT,ENTERINGTIME)"
                        + "VALUES(?,?,?,?,?,?,?,?,?,?)";
                stmt = conn.prepareStatement(sql);
                HashMap<String, Object> hm = valueMaplist.get(i);
                String CREATETIME = (String) hm.get("CREATETIME");
                int SORTID = Integer.parseInt((String) hm.get("SORTID"));
                String TITLE = (String) hm.get("TITLE");
                StringReader titleReader = new StringReader(TITLE);
                String CONTENTS = (String) hm.get("CONTENTS");
                StringReader contentsReader = new StringReader(CONTENTS);
//            Clob TITLE = new SerialClob(((String) hm.get("TITLE")).toCharArray());
//            Clob CONTENTS = new SerialClob(((String) hm.get("CONTENTS")).toCharArray());
                String URL = (String) hm.get("URL");
                long PVIEWS = Long.parseLong((String) hm.get("PVIEWS"));
                long LIKENUM = Long.parseLong((String) hm.get("LIKENUM"));
                long HOTVALUE = Long.parseLong((String) hm.get("HOTVALUE"));
                String HOT_SORT = (String) hm.get("HOT_SORT");
                String ENTERINGTIME = (String) hm.get("ENTERINGTIME");
//            String sql = "INSERT INTO  ZBMS_TX_DAYHOTTOPIC (CREATETIME ,TITLE ,CONTENTS,URL,PVIEWS,LIKENUM,HOTVALUE,HOT_SORT,ENTERINGTIME)"
//                    + "VALUES('" + CREATETIME + "'," + TITLE + "," + CONTENTS + ",'" + URL + "'," + PVIEWS + "," + LIKENUM + "," + HOTVALUE + ",'" + HOT_SORT + "','" + ENTERINGTIME + "',)";
                stmt.setString(1, CREATETIME);
                stmt.setInt(2, SORTID);
                stmt.setCharacterStream(3, titleReader, TITLE.length());
                stmt.setCharacterStream(4, contentsReader, CONTENTS.length());
                stmt.setString(5, URL);
                stmt.setLong(6, PVIEWS);
                stmt.setLong(7, LIKENUM);
                stmt.setLong(8, HOTVALUE);
                stmt.setString(9, HOT_SORT);
                stmt.setString(10, ENTERINGTIME);
                int a = stmt.executeUpdate();
                logger.info("sucess : " + a);
                stmt.close();
            }
        } catch (Exception e) {
            logger.error(e.getStackTrace(), e);
        } finally {
            stmt.close();
        }

    }

    public static void close() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        getConnection();

        System.out.println("haha");
//        insert();
        select();
        System.out.println("haha1");
        close();
    }
}
